SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC [sproceHomeLoanCalculationForThreeYears] 75, 0
--Update tblHLRequest SET HLLockIn = 1 Where HLId = 68
--select * from tblHLRequest Where HLId = 67 And RefCEUserId = 0
--select top 10 * from tblHLRequest Order By ModifiedDate DESC
--select * from tblHLTempResult Where RefHLId = 75
ALTER procedure [dbo].[sproceHomeLoanCalculationForThreeYears]
	@hlId bigint,
	@refceuserid bigint
AS

declare @rate1 float
declare @rate2 float
declare @rate3 float
declare @rate4 float
declare @noofyear smallint
declare @loanamt int
declare @propertytype smallint
declare @loanratetype smallint
declare @propertystatus smallint

declare @islockin bit
declare @isoffset bit

declare @HLPackageId int
declare @HLPackageNo int
declare @ProviderCompanyId int
declare @CXPackageName varchar(120)
declare @Y1Rate float 
declare @Y1SORMonth smallint 
declare @Y1SORRate float
declare @Y1SIBORMonth smallint
declare @Y1SIBORRate float
declare @Y2Rate float
declare @Y2SORMonth smallint
declare @Y2SORRate float
declare @Y2SIBORMonth smallint
declare @Y2SIBORRate float
declare @Y3Rate float
declare @Y3SORMonth smallint
declare @Y3SORRate float
declare @Y3SIBORMonth smallint
declare @Y3SIBORRate float
declare @Y4Rate float
declare @Y4SORMonth smallint
declare @Y4SORRate float
declare @Y4SIBORMonth smallint
declare @Y4SIBORRate float

declare @y1monthlyrepayment float
declare @y2monthlyrepayment float
declare @y3monthlyrepayment float
declare @y4monthlyrepayment float

declare @output varchar(max)

	--set @output = '<table>';
	--set @output = @output + '<th>Package</th><th>Total Interest</th><th>Y1 Monthly Repayments</th><th>Y2 Monthly Repayments</th><th>Y3 Monthly Repayments</th><th>ThereAfter Monthly Repayments</th>'
	
	DELETE FROM tblHLTempResult  Where RefHLId = @hlId And RefCEUserId = @refceuserid

	--HLAmount = 'Loan Amount'
	--HLMortgageType = 'New Loan' or 'Refinancing'
	--HLPropertyType = 'HDB' or 'Private'
	--HLProperty = 'Completed' or 'UnCompleted'
	--HLLoanTenure = 'Loan Duration'
	--HLInterestRate = 'Fixed' or 'Floating'
	
	Select @loanamt = HLAmount, @noofyear = HLLoanTenure, @propertytype = HLPropertyType, 
			@loanratetype = HLInterestRate, @propertystatus = HLProperty,
			@islockin = HLLockIn, @isoffset = HLOffsetSchemes 
	FROM tblHLRequest Where HLId = @hlId And RefCEUserId = @refceuserid

	Select @propertytype PROPERTYTYPE, @loanratetype PACKAGETYPE, @islockin ISLOCKIN, @PropertyStatus PROPERTYSTATUS
	--Select * from tblHLPackage Where PropertyType = @propertytype AND
	--	PackageType = @loantype AND
	--	IsLockIn = @islockin AND
	--	PropertyStatus = @propertystatus AND
	--	[Status] = 1
		
	Declare curHLPackage cursor for
		Select HLPackageId, HLPackageNo, ProviderCompanyID, CXPackageName, 
		Y1Rate, Y1SORMonth, Y1SIBORMonth,
		Y2Rate, Y2SORMonth, Y2SIBORMonth,
		Y3Rate, Y3SORMonth, Y3SIBORMonth,
		Y4Rate, Y4SORMonth, Y4SIBORMonth
		from tblHLPackage
		WHERE (PropertyType = @propertytype OR PropertyType = 3) AND
		PackageType = @loanratetype AND
		IsLockIn = @islockin AND
		(PropertyStatus = @propertystatus OR PropertyStatus = 2) AND
		[Status] = 1

	Open curHLPackage 
		fetch next from curHLPackage into @HLPackageId, @HLPackageNo, @ProviderCompanyID, @CXPackageName, 
		@Y1Rate, @Y1SORMonth, @Y1SIBORMonth,
		@Y2Rate, @Y2SORMonth, @Y2SIBORMonth,
		@Y3Rate, @Y3SORMonth, @Y3SIBORMonth,
		@Y4Rate, @Y4SORMonth, @Y4SIBORMonth
		WHILE (@@fetch_status <> -1)
		BEGIN	
			IF @Y1SIBORMonth > 0
				BEGIN
					Select @Y1SIBORRate = SIBORRate from tblHLRate WHERE [MonthOfRate] = @Y1SIBORMonth AND [Status] = 1
				END
			ELSE
				BEGIN
					Select @Y1SIBORRate = 0
				END
				
			IF @Y2SIBORMonth > 0
				BEGIN
					Select @Y2SIBORRate = SIBORRate from tblHLRate WHERE [MonthOfRate] = @Y2SIBORMonth AND [Status] = 1
				END
			ELSE
				BEGIN
					Select @Y2SIBORRate = 0
				END
				
			IF @Y3SIBORMonth > 0
				BEGIN
					Select @Y3SIBORRate = SIBORRate from tblHLRate WHERE [MonthOfRate] = @Y3SIBORMonth AND [Status] = 1
				END
			ELSE
				BEGIN
					Select @Y3SIBORRate = 0
				END
				
			IF @Y4SIBORMonth > 0
				BEGIN
					Select @Y4SIBORRate = SIBORRate from tblHLRate WHERE [MonthOfRate] = @Y4SIBORMonth AND [Status] = 1
				END
			ELSE
				BEGIN
					Select @Y4SIBORRate = 0
				END
				
			IF @Y1SORMonth > 0
				BEGIN
					Select @Y1SORRate = SORRate from tblHLRate WHERE [MonthOfRate] = @Y1SORMonth AND [Status] = 1
				END
			ELSE
				BEGIN
					Select @Y1SORRate = 0
				END
				
			IF @Y2SORMonth > 0
				BEGIN
					Select @Y2SORRate = SORRate from tblHLRate WHERE [MonthOfRate] = @Y2SORMonth AND [Status] = 1
				END
			ELSE
				BEGIN
					Select @Y2SORRate = 0
				END
				
			IF @Y3SORMonth > 0
				BEGIN
					Select @Y3SORRate= SORRate from tblHLRate WHERE [MonthOfRate] = @Y3SORMonth AND [Status] = 1
				END
			ELSE
				BEGIN
					Select @Y3SORRate = 0
				END
				
			IF @Y4SORMonth > 0
				BEGIN
					Select @Y4SORRate= SORRate from tblHLRate WHERE [MonthOfRate] = @Y4SORMonth AND [Status] = 1
				END
			ELSE
				BEGIN
					Select @Y4SORRate = 0
				END
			Select @rate1 = @Y1Rate + @Y1SIBORRate + @Y1SORRate
			Select @rate2 = @Y2Rate + @Y2SIBORRate + @Y2SORRate
			Select @rate3 = @Y3Rate + @Y3SIBORRate + @Y3SORRate
			Select @rate4 = @Y4Rate + @Y4SIBORRate + @Y4SORRate
			
			Select @CXPackageName CXPackageName, @Y1Rate Y1Rate, @Y1SIBORMonth Y1SIBORMonth, @Y1SIBORRate Y1SIBORRate, @Y1SORMonth Y1SORMonth, @Y1SORRate Y1SORRate, 
			@Y2Rate Y2Rate, @Y2SIBORMonth Y2SIBORMonth, @Y2SIBORRate Y2SIBORRate, @Y2SORMonth Y2SORMonth, @Y2SORRate Y2SORRate, 
			@Y3Rate Y3Rate, @Y3SIBORMonth Y3SIBORMonth, @Y3SIBORRate Y3SIBORRate, @Y3SORMonth Y3SORMonth, @Y3SORRate Y3SORRate, 
			@Y4Rate Y4Rate, @Y4SIBORMonth Y4SIBORMonth, @Y4SIBORRate Y4SIBORRate, @Y4SORMonth Y4SORMonth, @Y4SORRate Y4SORRate
		
			select @rate1 = (@rate1/100)
			select @rate2 = (@rate2/100)
			select @rate3 = (@rate3/100)
			select @rate4 = (@rate4/100)

			
			declare @totalperiod int
			declare @curperiod int
			declare @lastperiod int
			declare @curyear int
			declare @curinterestamt float
			declare @totalinterestforthreeyear float

			declare @outamt float
			declare @pmt float
			declare @principlpayment float
			select @curperiod = 1
			select @lastperiod = 1;
			select @curyear = 1
			select @totalperiod = @noofyear * 12
			select @outamt = ROUND(@loanamt,2)
			select @rate1 Rate1, @rate2 Rate2, @rate3 Rate3, @rate4 Rate4, @outamt OutstandingAmount, @totalperiod TotalPeriod
			
			SET @totalinterestforthreeyear = 0
			
	WHILE(@curperiod <= 48)
	BEGIN
		IF @curyear = 1
			BEGIN
				if(@curperiod%12 = 1)
				begin
					select @pmt = ROUND(dbo.PMT(@rate1/12, @totalperiod , @outamt), 2)
					select @y1monthlyrepayment = @pmt
					--select @y1monthlyrepayment Y1MONTHLYREPAYMENT
				end 
				select @curinterestamt = ROUND((@outamt * @rate1)/12, 2)
				select @totalinterestforthreeyear = @totalinterestforthreeyear + @curinterestamt
			END
		ELSE IF @curyear = 2
			BEGIN
				if(@curperiod%12 = 1 and @curperiod = 13)
				begin
					select @lastperiod = (@curyear-1) * 12
					select @pmt = ROUND(dbo.PMT(@rate2/12, @totalperiod - @lastperiod , @outamt), 2)
					select @y2monthlyrepayment = @pmt
					--select @y2monthlyrepayment Y2MONTHLYREPAYMENT
				end

				select @curinterestamt = ROUND((@outamt * @rate2)/12, 2)
				select @totalinterestforthreeyear = @totalinterestforthreeyear + @curinterestamt
			END
		ELSE IF @curyear = 3
			BEGIN
				if(@curperiod%12 = 1 and @curperiod = 25)
				begin
					select @lastperiod = (@curyear-1) * 12
					select @pmt = ROUND(dbo.PMT(@rate3/12, @totalperiod - @lastperiod , @outamt), 2)
					select @y3monthlyrepayment = @pmt
					--select @y3monthlyrepayment Y3MONTHLYREPAYMENT
				end
				select @curinterestamt = ROUND((@outamt * @rate3)/12, 2)
				select @totalinterestforthreeyear = @totalinterestforthreeyear + @curinterestamt
				
			END
		ELSE IF @curyear > 3
			BEGIN
				if(@curperiod%12 = 1 and @curperiod = 37)
				begin
					select @lastperiod = (@curyear-1) * 12
					select @pmt = ROUND(dbo.PMT(@rate4/12, @totalperiod - @lastperiod , @outamt), 2)
					select @y4monthlyrepayment = @pmt
					--select @y4monthlyrepayment Y4MONTHLYREPAYMENT
				end
				select @curinterestamt = ROUND((@outamt * @rate4)/12, 2)
			END
		
		--select @curinterestamt CURINTERESTAMT, @curyear CURYEAR, @totalinterestforthreeyear TOTALINTERESTFORTHREEYEAR
		select @principlpayment = @pmt - @curinterestamt

		select @outamt = @outamt - @principlpayment
		
		if (@curperiod = 48)
		BEGIN
			select @CXPackageName CXPackageName, @totalinterestforthreeyear TOTALINTERESTFORTHREEYEAR,
			@y1monthlyrepayment Y1MONTHLYREPAYMENT,
			@y2monthlyrepayment Y2MONTHLYREPAYMENT,
			@y3monthlyrepayment Y3MONTHLYREPAYMENT,
			@y4monthlyrepayment Y4MONTHLYREPAYMENT
			
			INSERT INTO tblHLTempResult
					( [HLPackageId]
					  ,[HLPackageNo]
					  ,[ProviderCompanyID]
					  ,[CXPackageName]
					  ,[ThreeYearInterest]
					  ,[IsLockIn]
					  ,[Y1Rate]
					  ,[Y1SORMonth]
					  ,[Y1SORRate]
					  ,[Y1SIBORMonth]
					  ,[Y1SIBORRate]
					  ,[Y2Rate]
					  ,[Y2SORMonth]
					  ,[Y2SORRate]
					  ,[Y2SIBORMonth]
					  ,[Y2SIBORRate]
					  ,[Y3Rate]
					  ,[Y3SORMonth]
					  ,[Y3SORRate]
					  ,[Y3SIBORMonth]
					  ,[Y3SIBORRate]
					  ,[Y4Rate]
					  ,[Y4SORMonth]
					  ,[Y4SORRate]
					  ,[Y4SIBORMonth]
					  ,[Y4SIBORRate]
					  ,[Y1Installment]
					  ,[Y2Installment]
					  ,[Y3Installment]
					  ,[Y4Installment]
					  ,[RefHLId]
					  ,[RefCEUserID])
			VALUES (  @HLPackageId,
					  @HLPackageNo,
					  @ProviderCompanyId,
					  @CXPackageName,
					  @totalinterestforthreeyear,
					  @islockin,
					  @Y1Rate,
					  @Y1SORMonth,
					  @Y1SORRate,
					  @Y1SIBORMonth,
					  @Y1SIBORRate,
					  @Y2Rate,
					  @Y2SORMonth,
					  @Y2SORRate,
					  @Y2SIBORMonth,
					  @Y2SIBORRate,
					  @Y3Rate,
					  @Y3SORMonth,
					  @Y3SORRate,
					  @Y3SIBORMonth,
					  @Y3SIBORRate,
					  @Y4Rate,
					  @Y4SORMonth,
					  @Y4SORRate,
					  @Y4SIBORMonth,
					  @Y4SIBORRate,
					  @y1monthlyrepayment,
					  @y2monthlyrepayment,
					  @y3monthlyrepayment,
					  @y3monthlyrepayment,
					  @hlId,
					  @refceuserid)
					  
		END
		if(@outamt < 1)
			break
		else
			if (@curperiod >= 12)
				begin
					select @curyear = (@curperiod/12) + 1 
					select @curperiod = @curperiod + 1
				end
			else
				begin
					select @curperiod = @curperiod + 1
					select @curyear = 1
				end
		END
		fetch next from curHLPackage into @HLPackageId, @HLPackageNo, @ProviderCompanyID, @CXPackageName, 
		@Y1Rate, @Y1SORMonth, @Y1SIBORMonth,
		@Y2Rate, @Y2SORMonth, @Y2SIBORMonth,
		@Y3Rate, @Y3SORMonth, @Y3SIBORMonth,
		@Y4Rate, @Y4SORMonth, @Y4SIBORMonth
	END
	Close curHLPackage
	Deallocate curHLPackage
	
--select @output + '</table>'